﻿<%@ WebHandler Language="C#" Class="MfgorderMaint" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Data;
using BusinessRule.DTO;
using DatabaseAccess.DBUtility;
using System.Collections.Generic;
using System.Web.SessionState;
using System.Web.SessionState;

public class MfgorderMaint : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{

    public void ProcessRequest(HttpContext context)
    {
        if (context.Request.QueryString["type"] != null && context.Request.QueryString["type"] == "GetUserInfo")
        {
            string json = "{}";
            if (context.Session["CurrentUserInfo"] != null)
            {
                json = JsonConvert.SerializeObject(context.Session["CurrentUserInfo"]);
            }
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //查询分厂
        if (context.Request.QueryString["type"] != null && context.Request.QueryString["type"] == "GetFactory")
        {
            string sql = "select f.factoryid,f.factoryname from factory f";
            
            DataTable dtResult = DatabaseHelper.Query_ReturnDatTable(sql);

            foreach (DataColumn col in dtResult.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            
            var json = JsonConvert.SerializeObject(dtResult);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
        
        //查询
        if (context.Request.QueryString["type"] != null && context.Request.QueryString["type"] == "GetData")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();

            if (context.Request.Form["page"] != null)
            {
                para["currentPage"] = context.Request.Form["page"];
            }
            if (context.Request.Form["rows"] != null)
            {
                para["pageSize"] = context.Request.Form["rows"];
            }

            var a = context.Session["CurrentUserInfo"];
            
            string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,mfg.qty,f.factoryname,pb.productname,p.description,mfg.plannedcompletiondate origindate,
       to_char(mfg.zhuigandate,'yyyy-mm-dd') zhuigandate,
       to_char(mfg.tiaozhengdate,'yyyy-mm-dd') tiaozhengdate,
       NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate) - ROUND(NVL(wfs.totaltime,0) * mfg.qty / 720) startdate,
       NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate) plannedcompletiondate,
       NVL(wfs.totaltime,0) * mfg.qty totalworktime
       --cst.worktime finishworktime
FROM mfgorder mfg
LEFT JOIN productbase pb1 ON pb1.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,mfg.productid)
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid 
--查总工时
LEFT JOIN 
(
   SELECT wfs.workflowid,SUM(sc.durationperunit) totaltime
   FROM workflowstep wfs
   LEFT JOIN stepschedulingdetail sc ON sc.stepid = wfs.workflowstepid
   GROUP BY workflowid
) wfs ON wfs.workflowid = p.workflowid
--查已完工工时
/*LEFT JOIN
(
  SELECT cc.mfgorderid,SUM(sc.durationperunit * cst.qty) worktime
  FROM containersteptimesheet cst
  LEFT JOIN containercurrentinfo cc ON cst.containerid = cc.containerid
  LEFT JOIN stepschedulingdetail sc ON sc.stepid = cst.workflowstepid
  LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cc.mfgorderid
  LEFT JOIN factory f ON f.factoryid = cc.originalfactoryid
  WHERE 1=1
  GROUP BY cc.mfgorderid
) cst ON cst.mfgorderid = mfg.mfgorderid*/
INNER JOIN
(
  SELECT DISTINCT cci.mfgorderid
  FROM containercurrentinfo cci
  WHERE cci.qty > 0 AND cci.isinstore <> 1 AND cci.mfgorderid IS NOT NULL
) mc ON mc.mfgorderid = mfg.mfgorderid
WHERE mfg.plannedcompletiondate IS NOT NULL     
";
            if (context.Request.Form["mfgordername"].ToString() != "空")
            {
                sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%' ", context.Request.Form["mfgordername"]);
            }

            if (context.Request.Form["productname"].ToString() != "空")
            {
                sql += string.Format(" AND pb.productname LIKE '%{0}%' ", context.Request.Form["productname"]);
            }

            if (context.Request.Form["factoryid"].ToString() != "全部")
            {
                sql += string.Format(" AND f.factoryid = '{0}' ", context.Request.Form["factoryid"]);
            }


            if (context.Request.Form["startdate"].ToString() != "空")
            {
                sql += string.Format(" AND TRUNC(NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate)) >= to_date('{0}','YYYY/MM/DD') ", context.Request.Form["startdate"]);
            }

            if (context.Request.Form["enddate"].ToString() != "空")
            {
                sql += string.Format(" AND TRUNC(NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate)) <= to_date('{0}','YYYY/MM/DD') ", context.Request.Form["enddate"]);
            }

            uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
            paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["page"].ToString());
            paraPage.PageSize = Convert.ToInt32(context.Request.Form["rows"].ToString());
            paraPage.strSQL = sql;

            uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);

            DataTable dtResult = result.DBTable;

            foreach (DataColumn col in dtResult.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }

            var json = JsonConvert.SerializeObject(dtResult);

            json = "{\"data\":" + json.ToString()
                    + ",\"count\":" + result.RowCount
                    + ",\"code\":" + "0"
                    + ",\"msg\":" + "0"
                    + "}";

            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //更新
        if (context.Request.QueryString["type"] != null && context.Request.QueryString["type"] == "Edit")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();

            //调整日期
            if (context.Request.Form["mfgorderid"] != null)
            {
                para["mfgorderid"] = context.Request.Form["mfgorderid"];
            }

            //调整日期
            if (context.Request.Form["tiaozhengdate"] != null)
            {
                para["tiaozhengdate"] = context.Request.Form["tiaozhengdate"];
            }

            //追赶日期
            if (context.Request.Form["zhuigandate"] != null)
            {
                para["zhuigandate"] = context.Request.Form["zhuigandate"];
            }

            string sql = string.Format("UPDATE mfgorder mfg set mfg.zhuigandate = to_date('{0}','yyyy/mm/dd'),mfg.tiaozhengdate = to_date('{1}','yyyy/mm/dd') WHERE mfg.mfgorderid = '{2}'",
                para["zhuigandate"], para["tiaozhengdate"], para["mfgorderid"]
                );
            
            
            int code = 0;
            string msg = "";
            string data = "[]";
            
            try
            {
                int result = DatabaseHelper.ExecuteSql(sql);
                if (result > 0)
                {
                    //获取更新成功后的【开工日期】及【完工日期】
                    data = GetUpdatedData(context.Request.Form["mfgorderid"]);
                    code = 1;
                    msg = "更新成功";
                }
            }
            catch (Exception e)
            {
                code = 0;
                msg = "更新失败";
            }
            
            var json = "{\"msg\":\"" + msg + "\",\"code\":" + code + ",\"data\":" + data +  "}";
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
    }

    //获取更新成功后的订单信息
    private string GetUpdatedData(string mfgorderid) {
        string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,mfg.qty,f.factoryname,pb.productname,p.description,mfg.plannedcompletiondate origindate,
       to_char(mfg.zhuigandate,'yyyy-mm-dd') zhuigandate,
       to_char(mfg.tiaozhengdate,'yyyy-mm-dd') tiaozhengdate,
       NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate) - ROUND(NVL(wfs.totaltime,0) * mfg.qty / 720) startdate,
       NVL(NVL(mfg.tiaozhengdate,mfg.zhuigandate),mfg.plannedcompletiondate) plannedcompletiondate,
       NVL(wfs.totaltime,0) * mfg.qty totalworktime
FROM mfgorder mfg
LEFT JOIN productbase pb1 ON pb1.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,mfg.productid)
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid 
--查总工时
LEFT JOIN 
(
   SELECT wfs.workflowid,SUM(sc.durationperunit) totaltime
   FROM workflowstep wfs
   LEFT JOIN stepschedulingdetail sc ON sc.stepid = wfs.workflowstepid
   GROUP BY workflowid
) wfs ON wfs.workflowid = p.workflowid
INNER JOIN
(
  SELECT DISTINCT cci.mfgorderid
  FROM containercurrentinfo cci
  WHERE cci.qty > 0 AND cci.isinstore <> 1 AND cci.mfgorderid IS NOT NULL
) mc ON mc.mfgorderid = mfg.mfgorderid
WHERE mfg.plannedcompletiondate IS NOT NULL     
";
        sql += string.Format(" AND mfg.mfgorderid = '{0}'", mfgorderid);

        DataTable dtResult = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        return JsonConvert.SerializeObject(dtResult);
    }
    
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}